﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Global.Class.Library;

namespace DataAccessLayer
{
    public class CommonStoreProcedure
    {
        public CommonStoreProcedure()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure)
                {
                    this.CreateFunction_FNSplitUpIds();
                }

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }



        private bool CreateFunction_FNSplitUpIds()
        {
            string sqlQuery = "";
            try
            {
                sqlQuery = " (@Param_Ids varchar(5000)) RETURNS @Id_Table TABLE(IDField int) ";
                sqlQuery = sqlQuery + " WITH ENCRYPTION " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + " AS " + char.ConvertFromUtf32(13);

                sqlQuery = sqlQuery + "   BEGIN    " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "       IF (LEN(@Param_Ids) <= 0)   " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "           RETURN  " + char.ConvertFromUtf32(13);

                sqlQuery = sqlQuery + "       DECLARE @CommaPos smallint  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "       SET @CommaPos = CHARINDEX(';', RTRIM(LTRIM(@Param_Ids)))    " + char.ConvertFromUtf32(13);

                sqlQuery = sqlQuery + "       IF @CommaPos = 0  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "           INSERT INTO @Id_Table VALUES(CONVERT(BIGINT, RTRIM(LTRIM(@Param_Ids))))  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "       ELSE   " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "           BEGIN  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "               WHILE LEN(@Param_Ids) > 1  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                   BEGIN  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                       SET @CommaPos = CHARINDEX(';', RTRIM(LTRIM(@Param_Ids)))  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                       INSERT INTO @Id_Table VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1))) " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                       SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                       SET @CommaPos = CHARINDEX(';', RTRIM(LTRIM(@Param_Ids)))  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                       IF @CommaPos = 0  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                           BEGIN " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                               INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                               BREAK  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                           END  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "                   END  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "           END  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "       RETURN  " + char.ConvertFromUtf32(13);
                sqlQuery = sqlQuery + "   END " + char.ConvertFromUtf32(13);

                return SQLDatabase.CreateFunction("FNSplitUpIds", sqlQuery);
            }
            catch
            {
                return false;
            }
        }








    }
}
